package com.wande.dataplatform.filecollection.parser.impl;

import cn.hutool.core.util.StrUtil;
import com.wande.dataplatform.filecollection.common.enums.FileCollectionErrorCode;
import com.wande.dataplatform.filecollection.common.exception.FileCollectionException;
import com.wande.dataplatform.filecollection.domain.dto.FieldInfo;
import com.wande.dataplatform.filecollection.parser.AbstractFileParser;
import com.wande.dataplatform.filecollection.parser.ParserConfig;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.io.*;
import java.util.*;
import java.util.function.Consumer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL脚本解析器
 * 解析INSERT语句提取数据
 * 支持批量INSERT语句
 * 处理特殊字符和转义
 *
 * @author dataplatform
 * @date 2025-01-24
 */
@Slf4j
@Component
public class SqlScriptParser extends AbstractFileParser {

    private static final int SAMPLE_SIZE = 100;

    /**
     * INSERT语句正则表达式
     * 匹配: INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...), (val1, val2, ...), ...
     */
    private static final Pattern INSERT_PATTERN = Pattern.compile(
            "INSERT\\s+INTO\\s+([\\w`]+)\\s*\\(([^)]+)\\)\\s*VALUES\\s*(.+)",
            Pattern.CASE_INSENSITIVE | Pattern.DOTALL
    );

    /**
     * VALUES子句正则表达式
     * 匹配: (val1, val2, ...), (val1, val2, ...)
     */
    private static final Pattern VALUES_PATTERN = Pattern.compile(
            "\\(([^)]+)\\)",
            Pattern.DOTALL
    );

    @Override
    public String getSupportedFileType() {
        return "SQL";
    }

    @Override
    public List<FieldInfo> detectFields(InputStream inputStream, ParserConfig config) {
        validateInputStream(inputStream);
        validateConfig(config);

        List<FieldInfo> fields = new ArrayList<>();

        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(inputStream, config.getCharset()))) {

            String sql = readSqlContent(reader);
            Matcher matcher = INSERT_PATTERN.matcher(sql);

            if (!matcher.find()) {
                return fields;
            }

            // 提取列名
            String columnsStr = matcher.group(2);
            List<String> columns = parseColumns(columnsStr);

            // 提取VALUES子句
            String valuesStr = matcher.group(3);
            List<List<String>> valuesList = parseValues(valuesStr);

            // 收集样本数据用于类型推断
            Map<Integer, List<String>> samples = new HashMap<>();
            int sampleCount = 0;

            for (List<String> values : valuesList) {
                if (sampleCount >= SAMPLE_SIZE) {
                    break;
                }

                for (int i = 0; i < values.size() && i < columns.size(); i++) {
                    samples.computeIfAbsent(i, k -> new ArrayList<>()).add(values.get(i));
                }
                sampleCount++;
            }

            // 创建字段信息
            for (int i = 0; i < columns.size(); i++) {
                String fieldName = cleanFieldName(columns.get(i));
                List<String> fieldSamples = samples.getOrDefault(i, Collections.emptyList());
                String fieldType = inferFieldTypeFromSamples(fieldSamples);

                FieldInfo fieldInfo = createFieldInfo(fieldName, fieldType, i);
                if (!fieldSamples.isEmpty()) {
                    fieldInfo.setSampleValue(fieldSamples.get(0));
                }
                fields.add(fieldInfo);
            }

        } catch (IOException e) {
            log.error("Failed to detect SQL fields", e);
            throw new FileCollectionException(FileCollectionErrorCode.FILE_PARSE_ERROR, "Failed to detect SQL fields: " + e.getMessage());
        }

        return fields;
    }

    @Override
    public Iterator<Map<String, Object>> parse(InputStream inputStream, ParserConfig config) {
        validateInputStream(inputStream);
        validateConfig(config);

        try {
            BufferedReader reader = new BufferedReader(
                    new InputStreamReader(inputStream, config.getCharset()));

            String sql = readSqlContent(reader);
            Matcher matcher = INSERT_PATTERN.matcher(sql);

            if (!matcher.find()) {
                return Collections.emptyIterator();
            }

            // 提取列名
            String columnsStr = matcher.group(2);
            List<String> columns = parseColumns(columnsStr);

            // 提取VALUES子句
            String valuesStr = matcher.group(3);
            List<List<String>> valuesList = parseValues(valuesStr);

            return new SqlIterator(columns, valuesList, config);

        } catch (IOException e) {
            log.error("Failed to parse SQL file", e);
            throw new FileCollectionException(FileCollectionErrorCode.FILE_PARSE_ERROR, "Failed to parse SQL file: " + e.getMessage());
        }
    }

    @Override
    public void parseStream(InputStream inputStream, ParserConfig config, Consumer<Map<String, Object>> consumer) {
        validateInputStream(inputStream);
        validateConfig(config);

        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(inputStream, config.getCharset()))) {

            String sql = readSqlContent(reader);
            Matcher matcher = INSERT_PATTERN.matcher(sql);

            if (!matcher.find()) {
                return;
            }

            // 提取列名
            String columnsStr = matcher.group(2);
            List<String> columns = parseColumns(columnsStr);

            // 提取VALUES子句
            String valuesStr = matcher.group(3);
            List<List<String>> valuesList = parseValues(valuesStr);

            // 逐行处理
            for (List<String> values : valuesList) {
                Map<String, Object> rowData = new LinkedHashMap<>();

                for (int i = 0; i < columns.size() && i < values.size(); i++) {
                    String fieldName = cleanFieldName(columns.get(i));
                    String value = processFieldValue(values.get(i), config);
                    rowData.put(fieldName, value);
                }

                // 跳过空行
                if (config.getSkipEmptyRows() && isEmptyRow(rowData)) {
                    continue;
                }

                consumer.accept(rowData);
            }

        } catch (IOException e) {
            log.error("Failed to parse SQL file in stream mode", e);
            throw new FileCollectionException(FileCollectionErrorCode.FILE_PARSE_ERROR, "Failed to parse SQL file in stream mode: " + e.getMessage());
        }
    }

    @Override
    public boolean validate(InputStream inputStream, ParserConfig config) {
        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(inputStream, config.getCharset()))) {

            String sql = readSqlContent(reader);
            Matcher matcher = INSERT_PATTERN.matcher(sql);
            return matcher.find();

        } catch (Exception e) {
            log.warn("SQL validation failed: {}", e.getMessage());
            return false;
        }
    }

    /**
     * 读取SQL内容
     *
     * @param reader BufferedReader
     * @return SQL内容
     * @throws IOException IO异常
     */
    private String readSqlContent(BufferedReader reader) throws IOException {
        StringBuilder sb = new StringBuilder();
        String line;

        while ((line = reader.readLine()) != null) {
            // 跳过注释行
            line = line.trim();
            if (line.startsWith("--") || line.startsWith("#")) {
                continue;
            }

            // 移除行内注释
            int commentIndex = line.indexOf("--");
            if (commentIndex > 0) {
                line = line.substring(0, commentIndex);
            }

            sb.append(line).append(" ");
        }

        return sb.toString();
    }

    /**
     * 解析列名
     *
     * @param columnsStr 列名字符串
     * @return 列名列表
     */
    private List<String> parseColumns(String columnsStr) {
        List<String> columns = new ArrayList<>();
        String[] parts = columnsStr.split(",");

        for (String part : parts) {
            String column = part.trim();
            // 移除反引号
            column = column.replace("`", "");
            columns.add(column);
        }

        return columns;
    }

    /**
     * 解析VALUES子句
     *
     * @param valuesStr VALUES字符串
     * @return 值列表
     */
    private List<List<String>> parseValues(String valuesStr) {
        List<List<String>> result = new ArrayList<>();
        Matcher matcher = VALUES_PATTERN.matcher(valuesStr);

        while (matcher.find()) {
            String valueGroup = matcher.group(1);
            List<String> values = parseValueGroup(valueGroup);
            result.add(values);
        }

        return result;
    }

    /**
     * 解析单个值组
     *
     * @param valueGroup 值组字符串
     * @return 值列表
     */
    private List<String> parseValueGroup(String valueGroup) {
        List<String> values = new ArrayList<>();
        StringBuilder currentValue = new StringBuilder();
        boolean inString = false;
        char stringDelimiter = 0;
        boolean escaped = false;

        for (int i = 0; i < valueGroup.length(); i++) {
            char c = valueGroup.charAt(i);

            if (escaped) {
                currentValue.append(c);
                escaped = false;
                continue;
            }

            if (c == '\\') {
                escaped = true;
                continue;
            }

            if ((c == '\'' || c == '"') && !inString) {
                inString = true;
                stringDelimiter = c;
                continue;
            }

            if (c == stringDelimiter && inString) {
                inString = false;
                stringDelimiter = 0;
                continue;
            }

            if (c == ',' && !inString) {
                values.add(processValue(currentValue.toString().trim()));
                currentValue = new StringBuilder();
                continue;
            }

            currentValue.append(c);
        }

        // 添加最后一个值
        if (currentValue.length() > 0) {
            values.add(processValue(currentValue.toString().trim()));
        }

        return values;
    }

    /**
     * 处理值
     *
     * @param value 原始值
     * @return 处理后的值
     */
    private String processValue(String value) {
        if (StrUtil.isBlank(value)) {
            return "";
        }

        // 处理NULL
        if ("NULL".equalsIgnoreCase(value)) {
            return "";
        }

        // 移除引号
        if ((value.startsWith("'") && value.endsWith("'")) ||
            (value.startsWith("\"") && value.endsWith("\""))) {
            value = value.substring(1, value.length() - 1);
        }

        // 处理转义字符
        value = value.replace("\\'", "'");
        value = value.replace("\\\"", "\"");
        value = value.replace("\\n", "\n");
        value = value.replace("\\r", "\r");
        value = value.replace("\\t", "\t");
        value = value.replace("\\\\", "\\");

        return value;
    }

    /**
     * SQL迭代器
     */
    private class SqlIterator implements Iterator<Map<String, Object>> {
        private final List<String> columns;
        private final List<List<String>> valuesList;
        private final ParserConfig config;
        private int currentIndex;

        public SqlIterator(List<String> columns, List<List<String>> valuesList, ParserConfig config) {
            this.columns = columns;
            this.valuesList = valuesList;
            this.config = config;
            this.currentIndex = 0;
        }

        @Override
        public boolean hasNext() {
            return currentIndex < valuesList.size();
        }

        @Override
        public Map<String, Object> next() {
            if (!hasNext()) {
                throw new NoSuchElementException();
            }

            List<String> values = valuesList.get(currentIndex);
            currentIndex++;

            Map<String, Object> rowData = new LinkedHashMap<>();
            for (int i = 0; i < columns.size() && i < values.size(); i++) {
                String fieldName = cleanFieldName(columns.get(i));
                String value = processFieldValue(values.get(i), config);
                rowData.put(fieldName, value);
            }

            return rowData;
        }
    }
}
